﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<追加接口帐号中的产品>
-- =============================================
CREATE PROCEDURE [dbo].[proc_AB_Interface_Append]
(
	@id INT,
	@CompanyId INT,
	@ProductId VARCHAR(200)
)

AS

BEGIN
	DECLARE @New_ProductId VARCHAR(200)
	-- 将原有的产品ID提取出来
	SET @New_ProductId=(SELECT TOP 1 ProductId FROM AB_Interface WHERE id=@id AND CompanyId=@CompanyId)
	

	DECLARE @SourceSql   varchar(8000),@StrSeprate   varchar(100)
	SET @SourceSql=@ProductId+','
	SET @StrSeprate=','
	DECLARE @ch varchar(100)
	DECLARE @PName NVARCHAR(300)
	SET @PName=''
	WHILE(@SourceSql<>'')   
		BEGIN   
			set   @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)   
			--SELECT  @ch 
			--判断该公司的所有接口帐号是否已经添加过该产品
			IF (SELECT COUNT(0) FROM AB_Interface WHERE ProductId LIKE '%{'+@ch+'}%' AND CompanyId=@CompanyId AND syzh=0)>0 
				BEGIN 
					--SET @PName=@PName+','+(SELECT Title FROM Product WHERE id=@ch)
					SELECT Title FROM Product WHERE id=@ch
					RETURN 
				END
			ELSE 
				BEGIN --如果不重复 将原有产品ID和当前不重复的ID组合成新的字符串
					SET @New_ProductId=@New_ProductId+'{'+@ch+'}'
				END
			set   @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')  
		END 
	
	
		--更新接口帐号的产品ID 和 预存帐号的产品ID
		UPDATE [AB_Interface]
		SET [ProductId] = @New_ProductId
		WHERE id=@id OR syzh=@id
		
		IF LEN(@PName)>0
			SELECT @PName
		ELSE
			SELECT 1
	
	--SET NOCOUNT ON;

END
